package com.onlyxiahui.of.muc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class UserDAO {

	private static final Logger Log = LoggerFactory.getLogger(UserDAO.class);
	private static final String LOAD_OFFLINE_PRESENCE = "SELECT offlinePresence, offlineDate FROM ofPresence WHERE username=?";
	private static final String INSERT_OFFLINE_PRESENCE = "INSERT INTO ofPresence(username, offlinePresence, offlineDate) VALUES(?,?,?)";
	private static final String DELETE_OFFLINE_PRESENCE = "DELETE FROM ofPresence WHERE username=?";

	public static long getOfflineTime(String username) {
		long offlineDate = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(LOAD_OFFLINE_PRESENCE);
			statement.setString(1, username);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				// String offlinePresence = DbConnectionManager.getLargeTextField(rs, 1);
				// if (rs.wasNull()) {
				// offlinePresence = "";
				// }
				offlineDate = Long.parseLong(resultSet.getString(2).trim());
			}
		} catch (SQLException sqle) {
			Log.error(sqle.getMessage(), sqle);
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return offlineDate;
	}

	public static String getOfflineTimeString(String username) {
		String offlineDate = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DbConnectionManager.getConnection();
			statement = connection.prepareStatement(LOAD_OFFLINE_PRESENCE);
			statement.setString(1, username);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				// String offlinePresence = DbConnectionManager.getLargeTextField(rs, 1);
				// if (rs.wasNull()) {
				// offlinePresence = "";
				// }
				offlineDate = resultSet.getString(2).trim();
			}
		} catch (SQLException sqle) {
			Log.error(sqle.getMessage(), sqle);
		} finally {
			DbConnectionManager.closeConnection(resultSet, statement, connection);
		}
		return offlineDate;
	}

	public static void writeToDatabase(String username, String offlinePresence, Date offlinePresenceDate) {
		// delete existing offline presence (if any)
		deleteOfflinePresenceFromDB(username);

		// Insert data into the database.
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(INSERT_OFFLINE_PRESENCE);
			pstmt.setString(1, username);
			if (offlinePresence != null) {
				DbConnectionManager.setLargeTextField(pstmt, 2, offlinePresence);
			} else {
				pstmt.setNull(2, Types.VARCHAR);
			}
			pstmt.setString(3, StringUtils.dateToMillis(offlinePresenceDate));
			pstmt.execute();
		} catch (SQLException sqle) {
			Log.error("Error storing offline presence of user: " + username, sqle);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	public static void deleteOfflinePresenceFromDB(String username) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(DELETE_OFFLINE_PRESENCE);
			pstmt.setString(1, username);
			pstmt.execute();
		} catch (SQLException sqle) {
			Log.error(sqle.getMessage(), sqle);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}
}
